package com.softer.chengxin.excel.dao.provider;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.common.enums.FieldEnum;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author Wang Xinrui
 * @date 2019/1/10
 */
public class ExcelProvider {
    private static final String LIMIT = " LIMIT ";
    private static final String PAGE_PARAM = "page";
    private static final String EXCEL_NAME = "excelName";
    private static final String FIELDS = "fields";
    private static final String AREA = "area";
    private static final String QY_NAME = "qyName";
    private static final String STREET = "street";
    private static final String BEGIN_TIME = "beginTime";
    private static final String END_TIME = "endTime";
    private static final String DISTRICT = "district";
    private static final String REGISTER_ADDRESS = "registerAddress";
    private static final String INSURANCE = "insurance";
    private static final String CERTIFICATE = "certificate";
    private static final String DUTIES = "duties";
    private static final String DJ = "dj";
    private static final String YEAR = "year";
    private static final String PRO_NAME = "proName";
    private static final String INFO_TYPE = "infoType";
    private static final String DEPARTMENT = "department";
    private static final String SUBJECT = "subject";
    private static final String SELECT_COUNT = "select count(*)";
    private static final String SELECT_CID = "select c.id";
    private static final String C_NAME = ",c.`name` as ";
    /**
     * 获取报表列表
     */
    public String listExcels(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        Page page = (Page) map.get(PAGE_PARAM);
        String excelName = (String) map.get(EXCEL_NAME);
        sql.append("select id,excel_name as excelName,excelStr,excelObj,type,adder,gmt_create as gmtCreate,gmt_modified as gmtModified,excel_str_name as excelStrName");
        sql.append(" from excel_export_record");
        //只显示本人添加到数据
        sql.append(" where adder=#{loginName} and excel_type=#{excelType}");
        commonExcel(sql, excelName);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 获取报表列表数量
     */
    public String countExcel(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String excelName = (String) map.get(EXCEL_NAME);
        sql.append(SELECT_COUNT);
        sql.append(" from excel_export_record");
        //只显示本人添加的数据
        sql.append(" where adder=#{loginName} and excel_type=#{excelType}");
        commonExcel(sql, excelName);
        return sql.toString();
    }

    /**
     * 报表通用查询条件
     *
     * @param sql       语句
     * @param excelName 报表名
     */
    private void commonExcel(StringBuffer sql, String excelName) {
        if (StringUtils.isNotBlank(excelName)) {
            sql.append(" and excel_name like CONCAT('%',#{excelName},'%')");
        }
    }

    /**
     * 导出企业信息 数据（默认显示企业名）
     */
    public String listCorps(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String[] fields = (String[]) map.get(FIELDS);
        Page page = (Page) map.get(PAGE_PARAM);
        String area = (String) map.get(AREA);
        String qyName = (String) map.get(QY_NAME);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        Integer district = (Integer) map.get(DISTRICT);
        String registerAddress = (String) map.get(REGISTER_ADDRESS);
        //默认显示企业名
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(",c.name as ").append(field);
            } else if (FieldEnum.INSURANCE.getField().equals(field)) {
                sql.append(",d.insurance as ").append(field);
            } else if (FieldEnum.CERTIFICATE.getField().equals(field)) {
                sql.append(",d.certificate as ").append(field);
            } else if (FieldEnum.PROJECT_NUM.getField().equals(field)) {
                sql.append(",(select count(*) from company_project pro where pro.company_id=c.id) as project_num");
            } else if (FieldEnum.ALL_PERSON_NUM.getField().equals(field)) {
                sql.append(",(select count(*) from company_person p where p.company_id=c.id) as all_person_num");
            } else if (FieldEnum.REGISTER_TYPE.getField().equals(field)) {
                sql.append(",a.name as register_type");
            } else if (FieldEnum.SYSTEM_TYPE.getField().equals(field)) {
                sql.append(",b.name as system_type");
            } else if (FieldEnum.ORGANIZATION.getField().equals(field)) {
                // 党组织建设情况
                sql.append(",(CASE WHEN c.organization = '0' THEN '未成立党支部' WHEN c.organization = '1' THEN '成立党支部' ELSE '' END ) AS organization ");
            } else if (FieldEnum.THREE_SYSTEM.getField().equals(field)) {
                // 三体系认证
                sql.append(",(CASE WHEN c.three_system = '0' THEN '未通过' WHEN c.three_system = '1' THEN '通过' ELSE '' END ) AS three_system ");
            } else if (FieldEnum.DISTRICT.getField().equals(field)) {
                // 区（市，县）
                sql.append(",m.name as district");
            } else if (FieldEnum.STREET.getField().equals(field)) {
                // 街道
                sql.append(", s.name as street");
            } else {
                sql.append(",c.").append(field).append(" as ").append(field);
            }
        }
        sql.append(" from company_base_info c");
        sql.append(" left join dic_info a on c.register_type=a.code");
        sql.append(" left join dic_info b on c.system_type=b.code");
        sql.append(" left join district_street m on m.id=c.district");
        sql.append(" left join district_street s on s.id=c.street");
        sql.append(" LEFT JOIN ( SELECT company_id, COUNT(CASE WHEN insurance = 1 THEN 1 END) AS insurance,COUNT(CASE WHEN certificate=1 THEN 1 END) as certificate FROM company_person GROUP BY company_id ) d ON d.company_id = c.id");
        commonCorp(sql, area, district, registerAddress, qyName, street, beginTime, endTime);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 导出企业信息 数据数量
     */
    public String countCorp(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get(AREA);
        String registerAddress = (String) map.get(REGISTER_ADDRESS);
        String qyName = (String) map.get(QY_NAME);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        Integer district = (Integer) map.get(DISTRICT);
        sql.append(SELECT_COUNT);
        sql.append(" from company_base_info c");
        sql.append(" left join dic_info a on c.register_type=a.code");
        sql.append(" left join dic_info b on c.system_type=b.code");
        commonCorp(sql, area, district, registerAddress, qyName, street, beginTime, endTime);
        return sql.toString();
    }

    /**
     * 企业通用查询条件
     *
     * @param sql             语句
     * @param area            地区
     * @param district        区县
     * @param registerAddress 注册地
     * @param qyName          企业名
     */
    private void commonCorp(StringBuffer sql, String area, Integer district, String registerAddress, String qyName, String street, String beginTime, String endTime) {
        //只显示企业审批通过的数据
        sql.append(" where c.state in (2,9) ");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE c.community WHEN 0 THEN c.street in (").append(area).append(") ELSE c.community in (").append(area).append(") end)");
        }
        if (district != null) {
            sql.append(" and c.district=#{district}");
        }
        if (StringUtils.isNotBlank(registerAddress)) {
            sql.append(" and instr(c.register_address,#{registerAddress})>0");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and c.street=#{street}");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and c.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and c.gmt_create<=#{endTime}");
        }
    }

    /**
     * 导出企业人员信息数据（默认显示企业名，人员名）
     */
    public String listCorpPersons(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String[] fields = (String[]) map.get(FIELDS);
        String area = (String) map.get(AREA);
        Page page = (Page) map.get(PAGE_PARAM);
        Integer district = (Integer) map.get(DISTRICT);
        String qyName = (String) map.get(QY_NAME);
        String insurance = (String) map.get(INSURANCE);
        String certificate = (String) map.get(CERTIFICATE);
        String duties = (String) map.get(DUTIES);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，人员名
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(C_NAME).append(field);
            } else if (FieldEnum.PERSON_NAME.getField().equals(field)) {
                sql.append(",p.`name` as ").append(field);
            } else if (FieldEnum.DEPARTMENT_ID.getField().equals(field)) {
                sql.append(" ,(SELECT name FROM dic_info WHERE code = p.department_id) AS department_id ");
            } else {
                commonPerson(sql, field);
            }
        }
        sql.append(" from company_person p join company_base_info c on p.company_id = c.id ");
        sql.append(" LEFT JOIN dic_info a on p.education_level =a.`code`");
        sql.append(" LEFT JOIN dic_info b on p.duties=b.`code`");
        sql.append(" LEFT JOIN dic_info d ON p.major=d.`code`");
        sql.append(" WHERE ");
        commonCorpPerson(sql, area, district, qyName, insurance, certificate, duties, street, beginTime, endTime);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 导出企业人员信息数据数量
     */
    public String countCorpPerson(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        Integer district = (Integer) map.get(DISTRICT);
        String qyName = (String) map.get(QY_NAME);
        String insurance = (String) map.get(INSURANCE);
        String certificate = (String) map.get(CERTIFICATE);
        String duties = (String) map.get(DUTIES);
        String area = (String) map.get(AREA);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        sql.append(SELECT_COUNT);
        sql.append(" from company_person p join company_base_info c on p.company_id = c.id ");
        sql.append(" LEFT JOIN dic_info a on p.education_level =a.`code`");
        sql.append(" LEFT JOIN dic_info b on p.duties=b.`code`");
        sql.append(" LEFT JOIN dic_info d ON p.major=d.`code`");
        sql.append(" WHERE ");
        commonCorpPerson(sql, area, district, qyName, insurance, certificate, duties, street, beginTime, endTime);
        return sql.toString();
    }

    /**
     * 企业从业人员 通用查询条件
     *
     * @param sql         语句
     * @param area        地区
     * @param district    区县
     * @param qyName      企业名
     * @param insurance   五险一金
     * @param certificate 相关中等从业资格证书
     * @param duties      职务
     */
    private void commonCorpPerson(StringBuffer sql, String area, Integer district, String qyName, String insurance, String certificate, String duties, String street, String beginTime, String endTime) {
        //只显示在职人员的数据
        sql.append(" p.state=1");
        //查询企业从业人员
        sql.append(" and p.project_id=0");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE c.community WHEN 0 THEN c.street in (").append(area).append(") ELSE c.community in (").append(area).append(") end)");
        }
        if (district != null) {
            sql.append(" and c.district=#{district}");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(insurance)) {
            sql.append(" and p.insurance=#{insurance}");
        }
        if (StringUtils.isNotBlank(certificate)) {
            sql.append(" and p.certificate=#{certificate}");
        }
        if (StringUtils.isNotBlank(duties)) {
            sql.append(" and p.duties=#{duties}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and c.street=#{street}");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and p.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and p.gmt_create<=#{endTime}");
        }
    }


    /**
     * 人员查询的通用参数
     *
     * @param sql   SQL语句
     * @param field 参数
     */
    private void commonPerson(StringBuffer sql, String field) {
        if (FieldEnum.SEX.getField().equals(field)) {
            //性别
            sql.append(",(CASE WHEN p.sex = '10003' THEN '男' WHEN p.sex = '10004' THEN '女' ELSE '' END ) AS sex");
        } else if (FieldEnum.POLITICAL_STATUS.getField().equals(field)) {
            // 政治面貌
            sql.append(",(CASE WHEN p.political_status = '0' THEN '党员' WHEN p.political_status = '1' THEN '团员'  WHEN p.political_status = '2' THEN '群众' ELSE '' END ) AS political_status ");
        } else if (FieldEnum.COUNTRY_CODE.getField().equals(field)) {
            // 国籍
            sql.append(",(CASE WHEN p.country_code = '142' THEN '中华人民共和国' WHEN p.country_code = '1421' THEN '香港'  WHEN p.country_code = '1422' THEN '澳门' WHEN p.country_code = '1423' THEN '台湾' ELSE '其他国家(地区)' END ) AS country_code ");
        } else if (FieldEnum.INSURANCE.getField().equals(field)) {
            // 是否缴纳五险一金(1是，2否)
            sql.append(",(CASE WHEN p.insurance = '2' THEN '否' WHEN p.insurance = '1' THEN '是' ELSE '' END ) AS insurance ");
        } else if (FieldEnum.CERTIFICATE.getField().equals(field)) {
            //是否持有相关中等从业资格证书（0否，1是）
            sql.append(",(CASE WHEN p.certificate = '0' THEN '否' WHEN p.certificate = '1' THEN '是' ELSE '' END ) AS certificate ");
        } else if (FieldEnum.DUTIES.getField().equals(field)) {
            // 职务
            sql.append(",b.`name` as duties");
        } else if (FieldEnum.EDUCATION_LEVEL.getField().equals(field)) {
            //学历
            sql.append(",a.`name` as education_level");
        } else if (FieldEnum.MAJOR.getField().equals(field)) {
            //专业
            sql.append(",d.`name` as major");
        } else {
            sql.append(",p.").append(field).append(" as ").append(field);
        }
    }

    /**
     * 企业年度综合评级数据
     */
    public String listCorpRecords(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String[] fields = (String[]) map.get(FIELDS);
        String qyName = (String) map.get(QY_NAME);
        Page page = (Page) map.get(PAGE_PARAM);
        String area = (String) map.get(AREA);
        String dj = (String) map.get(DJ);
        String year = (String) map.get(YEAR);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        Integer district = (Integer) map.get(DISTRICT);
        //默认显示企业名，年度总分
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(C_NAME).append(field);
            } else if (FieldEnum.BUSINESS_lLICENSE_NO.getField().equals(field)) {
                sql.append(",c.business_license_no as ").append(field);
            } else if (FieldEnum.JC_SCORE.getField().equals(field)) {
                sql.append(",r.basic_score  as ").append(field);
            } else if (FieldEnum.YL_SCORE.getField().equals(field)) {
                sql.append(",r.excellent_score as ").append(field);
            }else if (FieldEnum.FM_SCORE.getField().equals(field)) {
                sql.append(",r.negative_score as ").append(field);
            } else if (DJ.equals(field)) {
                sql.append(",r.level as ").append(field);
            } else {
                sql.append(",r.").append(field).append(" as ").append(field);
            }
        }
        sql.append(" from score_composite r,company_base_info c");
        sql.append(" where r.company_id=c.id");
        commonCorpRecord(sql, area, qyName, dj, year, street, beginTime, endTime, district);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 企业年度综合评级数据 数量
     */
    public String countCorpRecord(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String qyName = (String) map.get(QY_NAME);
        String area = (String) map.get(AREA);
        String dj = (String) map.get(DJ);
        String year = (String) map.get(YEAR);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        Integer district = (Integer) map.get(DISTRICT);
        sql.append(SELECT_COUNT);
        sql.append(" from score_composite r,company_base_info c");
        sql.append(" where r.company_id=c.id");
        commonCorpRecord(sql, area, qyName, dj, year, street, beginTime, endTime, district);
        return sql.toString();
    }

    /**
     * 企业年度综合评级 通用查询条件
     *
     * @param sql    语句
     * @param area   地区
     * @param qyName 企业名
     * @param dj     等级
     * @param year   年份
     */
    private void commonCorpRecord(StringBuffer sql, String area, String qyName, String dj, String year, String street, String beginTime, String endTime, Integer district) {
        //只显示企业审批通过的数据
        sql.append(" and c.state in(2,9)");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE c.community WHEN 0 THEN c.street in (").append(area).append(") ELSE c.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(dj)) {
            sql.append(" and r.level=#{dj}");
        }
        if (StringUtils.isNotBlank(year)) {
            sql.append(" and r.year=#{year}");
        }
        if (district != null) {
            sql.append(" and c.district=#{district}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and c.street=#{street}");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and c.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and c.gmt_create<=#{endTime}");
        }
    }

    /**
     * 项目信息数据
     */
    public String listProjects(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String[] fields = (String[]) map.get(FIELDS);
        String qyName = (String) map.get(QY_NAME);
        String area = (String) map.get(AREA);
        Page page = (Page) map.get(PAGE_PARAM);
        String proName = (String) map.get(PRO_NAME);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，项目名
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(C_NAME).append(field);
            } else if (FieldEnum.PROJECT_NAME.getField().equals(field)) {
                sql.append(",p.project_name as ").append(field);
            } else if (FieldEnum.CHARGE_LOWER_LIMIT.getField().equals(field)) {
                //单位面积收费下限
                sql.append(",IFNULL((select least(multi_storey_fee_scale,high_rise_fee_scale,small_high_rise_fee_scale,villa_fee_scale,dot_fee_scale,other_fee_scale) from company_profit where project_id=p.project_id),0) as charge_lower_limit");
            } else if (FieldEnum.CHARGE_UPPER_LIMIT.getField().equals(field)) {
                //单位面积收费上限
                sql.append(",IFNULL((select greatest(multi_storey_fee_scale,high_rise_fee_scale,small_high_rise_fee_scale,villa_fee_scale,dot_fee_scale,other_fee_scale) from company_profit where project_id=p.project_id),0) as charge_upper_limit");
            } else if (FieldEnum.OLD.getField().equals(field)) {
                sql.append(",(CASE WHEN p.is_old = '0' THEN '否' WHEN p.is_old = '1' THEN '是' ELSE '' END ) AS is_old ");
            } else if (FieldEnum.INTELLIGENT.getField().equals(field)) {
                sql.append(",(CASE WHEN p.is_intelligent = '0' THEN '否' WHEN p.is_intelligent = '1' THEN '是' ELSE '' END ) AS is_intelligent ");
            } else if (FieldEnum.COMMITTEE.getField().equals(field)) {
                sql.append(",(CASE WHEN p.is_committee = '0' THEN '否' WHEN p.is_committee = '1' THEN '是' ELSE '' END ) AS is_committee ");
            } else if (FieldEnum.INDEMNIFICATORY.getField().equals(field)) {
                sql.append(",(CASE WHEN p.is_indemnificatory = '0' THEN '否' WHEN p.is_indemnificatory = '1' THEN '是' ELSE '' END ) AS is_indemnificatory ");
            } else if (FieldEnum.GET_TYPE.getField().equals(field)) {
                sql.append(",(SELECT name FROM dic_info WHERE code = p.get_type) AS get_type ");
            } else if (FieldEnum.PROPERTY_TYPE.getField().equals(field)) {
                sql.append(",(SELECT GROUP_CONCAT(name) FROM dic_info WHERE  FIND_IN_SET(code,p.property_type) ) AS property_type ");
            } else if (FieldEnum.DISTRICT.getField().equals(field)) {
                // 区（市，县）
                sql.append(",m.name as district");
            } else if (FieldEnum.STREET.getField().equals(field)) {
                // 街道
                sql.append(", s.name as street");
            } else {
                sql.append(",p.").append(field).append(" as ").append(field);
            }
        }
        sql.append(" from company_project p left join  company_base_info c on p.company_id=c.id");
        sql.append(" left join district_street m on m.id=p.district");
        sql.append(" left join district_street s on s.id=p.street");
        sql.append(" where 1=1 ");
        commonProject(sql, area, qyName, proName, district, street, beginTime, endTime);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 项目信息数据数量
     */
    public String countProject(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String qyName = (String) map.get(QY_NAME);
        String area = (String) map.get(AREA);
        String proName = (String) map.get(PRO_NAME);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        sql.append(SELECT_COUNT);
        sql.append(" from company_project p,company_base_info c");
        sql.append(" where  p.company_id=c.id");
        commonProject(sql, area, qyName, proName, district, street, beginTime, endTime);
        return sql.toString();
    }

    /**
     * 项目通用查询条件
     *
     * @param sql      语句
     * @param area     地区
     * @param qyName   企业名
     * @param proName  项目名
     * @param district 区县
     */
    private void commonProject(StringBuffer sql, String area, String qyName, String proName, Integer district, String street, String beginTime, String endTime) {
        //只导出项目审批通过的数据
        sql.append(" and p.state=2");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE p.community WHEN 0 THEN p.street in (").append(area).append(") ELSE p.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        if (district != null) {
            sql.append(" and p.district=#{district}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and p.street=#{street}");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and p.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and p.gmt_create<=#{endTime}");
        }
    }

    /**
     * 项目人员信息数据
     */
    public String listProjectPersons(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String[] fields = (String[]) map.get(FIELDS);
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Page page = (Page) map.get(PAGE_PARAM);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，项目名，人员名
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(C_NAME).append(field);
            } else if (FieldEnum.PROJECT_NAME.getField().equals(field)) {
                sql.append(",pro.project_name as ").append(field);
            } else if (FieldEnum.PERSON_NAME.getField().equals(field)) {
                sql.append(",p.`name` as ").append(field);
            } else if (FieldEnum.DEPARTMENT_ID.getField().equals(field)) {
                sql.append(",(CASE WHEN p.department_id = '1' THEN '客户服务'");
                sql.append(" WHEN p.department_id = '2' THEN '工程维修' ");
                sql.append(" WHEN p.department_id = '3' THEN '秩序维护' ");
                sql.append(" WHEN p.department_id = '4' THEN '环境维护' ");
                sql.append("ELSE '其他' END ) AS department_id ");
            } else {
                commonPerson(sql, field);
            }
        }
        sql.append(" from company_person p ");
        sql.append(" JOIN company_base_info c on p.company_id = c.id");
        sql.append(" JOIN company_project pro on p.project_id = pro.id");
        sql.append(" LEFT JOIN dic_info a on p.education_level =a.`code`");
        sql.append(" LEFT JOIN dic_info b on p.duties=b.`code`");
        sql.append(" LEFT JOIN dic_info d ON p.major=d.`code`");
        sql.append(" where ");
        commonProjectPerson(sql, area, qyName, proName, street, beginTime, endTime, district);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 项目人员信息数据数量
     */
    public String countProjectPerson(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        sql.append(SELECT_COUNT);
        sql.append(" from company_person p ");
        sql.append(" JOIN company_base_info c on p.company_id = c.id");
        sql.append(" JOIN company_project pro on p.project_id = pro.id");
        sql.append(" LEFT JOIN dic_info a on p.education_level =a.`code`");
        sql.append(" LEFT JOIN dic_info b on p.duties=b.`code`");
        sql.append(" LEFT JOIN dic_info d ON p.major=d.`code`");
        sql.append(" where ");
        commonProjectPerson(sql, area, qyName, proName, street, beginTime, endTime, district);
        return sql.toString();
    }

    /**
     * 项目人员通用查询条件
     *
     * @param sql     语句
     * @param area    地区
     * @param qyName  企业名
     * @param proName 项目名
     */
    private void commonProjectPerson(StringBuffer sql, String area, String qyName, String proName, String street, String beginTime, String endTime, Integer district) {
        //只显示在职项目人员的信息
        sql.append(" p.state=1");
        //查询人员表中项目id不为null的
        sql.append(" and p.project_id <>0");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE pro.community WHEN 0 THEN pro.street in (").append(area).append(") ELSE pro.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and pro.project_name like CONCAT('%',#{proName},'%')");
        }
        if (district != null) {
            sql.append(" and pro.district=#{district}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and pro.street=#{street}");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and p.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and p.gmt_create<=#{endTime}");
        }
    }

    /**
     * 项目物业用房信息数据
     */
    public String listProjectHouses(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String[] fields = (String[]) map.get(FIELDS);
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Page page = (Page) map.get(PAGE_PARAM);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，项目名
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(C_NAME).append(field);
            } else if (FieldEnum.PROJECT_NAME.getField().equals(field)) {
                sql.append(",p.project_name as ").append(field);
            } else {
                sql.append(",h.").append(field).append(" as ").append(field);
            }
        }
        sql.append(" from company_project p,company_housing h,company_base_info c");
        sql.append(" where p.project_id=h.project_id");
        sql.append(" and p.company_id=c.id");
        commonProjectHouse(sql, area, qyName, proName, street, beginTime, endTime, district);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 项目物业用房信息数据 数量
     */
    public String countProjectHouse(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        sql.append(SELECT_COUNT);
        sql.append(" from company_project p,company_housing h,company_base_info c");
        sql.append(" where p.project_id=h.project_id");
        sql.append(" and p.company_id=c.id");
        commonProjectHouse(sql, area, qyName, proName, street, beginTime, endTime, district);
        return sql.toString();
    }

    /**
     * 项目物业用房 通用查询条件
     *
     * @param sql     语句
     * @param area    地区
     * @param qyName  企业名
     * @param proName 项目名
     */
    private void commonProjectHouse(StringBuffer sql, String area, String qyName, String proName, String street, String beginTime, String endTime, Integer district) {
        //只显示项目审批通过的数据
        sql.append(" and p.state=2");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE p.community WHEN 0 THEN p.street in (").append(area).append(") ELSE p.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        if (district != null) {
            sql.append(" and p.district=#{district}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and p.street=#{street}");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and h.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and h.gmt_create<=#{endTime}");
        }
    }

    /**
     * 业主委员会信息数据
     */
    public String listProprietors(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String[] fields = (String[]) map.get(FIELDS);
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Page page = (Page) map.get(PAGE_PARAM);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，项目名,业委会委员名
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(C_NAME).append(field);
            } else if (FieldEnum.PROJECT_NAME.getField().equals(field)) {
                sql.append(",pro.project_name as ").append(field);
            } else if (FieldEnum.PERSON_NAME.getField().equals(field)) {
                sql.append(",p.`name` as ").append(field);
            } else if (FieldEnum.SEX.getField().equals(field)) {
                sql.append(",(CASE WHEN p.sex = '10003' THEN '男' WHEN p.sex = '10004' THEN '女' ELSE '' END ) AS sex");
            } else if (FieldEnum.EDUCATION_LEVEL.getField().equals(field)) {
                sql.append(",a.name as education_level");
            } else if (FieldEnum.DUTY.getField().equals(field)) {
                sql.append(",(SELECT name FROM dic_info WHERE code = p.duty) AS duty");
            } else if (FieldEnum.WORK_STATE.getField().equals(field)) {
                sql.append(",(CASE WHEN p.state = '0' THEN '离职' WHEN p.state = '1' THEN '个体' WHEN p.state = '2' THEN '退休' WHEN p.state = '3' THEN '在职' ELSE '其他' END ) AS work_state");
            } else if (FieldEnum.POLITICAL_STATUS.getField().equals(field)) {
                sql.append(",(CASE WHEN p.political_status = '0' THEN '党员' WHEN p.political_status = '1' THEN '团员'  WHEN p.political_status = '2' THEN '群众' ELSE '' END ) AS political_status ");
            } else {
                sql.append(",p.").append(field).append(" as ").append(field);
            }
        }
        sql.append(" from company_proprietor p join company_project pro on pro.project_id=p.project_id");
        sql.append(" join company_base_info c on pro.company_id=c.id");
        sql.append(" left join dic_info a on a.code= p.education_level");
        sql.append(" where ");
        commonProprietor(sql, area, qyName, proName, street, beginTime, endTime, district);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 业主委员会信息数据数量
     */
    public String countProprietor(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        sql.append(SELECT_COUNT);
        sql.append(" from company_proprietor p join company_project pro on pro.project_id=p.project_id");
        sql.append(" join company_base_info c on  pro.company_id=c.id");
        sql.append(" left join dic_info a on a.code= p.education_level");
        sql.append(" where ");
        commonProprietor(sql, area, qyName, proName, street, beginTime, endTime, district);
        return sql.toString();
    }

    /**
     * 业主委员会 通用查询条件
     *
     * @param sql     语句
     * @param area    地区
     * @param qyName  企业名
     * @param proName 项目名
     */
    private void commonProprietor(StringBuffer sql, String area, String qyName, String proName, String street, String beginTime, String endTime, Integer district) {
        //只显示项目审批通过的数据
        sql.append(" pro.state=2");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE pro.community WHEN 0 THEN pro.street in (").append(area).append(") ELSE pro.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and pro.project_name like CONCAT('%',#{proName},'%')");
        }
        if (district != null) {
            sql.append(" and pro.district=#{district}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and pro.street=#{street}");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and p.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and p.gmt_create<=#{endTime}");
        }
    }

    /**
     * 项目经营状况信息数据
     */
    public String listProjectProfits(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String[] fields = (String[]) map.get(FIELDS);
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Page page = (Page) map.get(PAGE_PARAM);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，项目名
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(C_NAME).append(field);
            } else if (FieldEnum.PROJECT_NAME.getField().equals(field)) {
                sql.append(",pro.project_name as ").append(field);
            } else {
                sql.append(",p.").append(field).append(" as ").append(field);
            }
        }
        sql.append(" from company_project pro,company_profit p,company_base_info c");
        sql.append(" where pro.project_id=p.project_id");
        sql.append(" and pro.company_id=c.id");
        commonProjectProfit(sql, area, qyName, proName, street, beginTime, endTime, district);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 项目经营状况信息数据数量
     */
    public String countProjectProfit(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        sql.append(SELECT_COUNT);
        sql.append(" from company_project pro,company_profit p,company_base_info c");
        sql.append(" where pro.project_id=p.project_id");
        sql.append(" and pro.company_id=c.id");
        commonProjectProfit(sql, area, qyName, proName, street, beginTime, endTime, district);
        return sql.toString();
    }

    /**
     * 项目经营状况 通用查询条件
     *
     * @param sql     语句
     * @param area    地区
     * @param qyName  企业名
     * @param proName 项目名
     */
    private void commonProjectProfit(StringBuffer sql, String area, String qyName, String proName, String street, String beginTime, String endTime, Integer district) {
        //只显示项目审批通过的数据
        sql.append(" and pro.state=2");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE pro.community WHEN 0 THEN pro.street in (").append(area).append(") ELSE pro.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and pro.project_name like CONCAT('%',#{proName},'%')");
        }
        if (district != null) {
            sql.append(" and pro.district=#{district}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and pro.street=#{street}");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and p.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and p.gmt_create<=#{endTime}");
        }
    }

    /**
     * 优良负面信息数据
     */
    public String listScoreDetails(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String[] fields = (String[]) map.get(FIELDS);
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Page page = (Page) map.get(PAGE_PARAM);
        Integer infoType = (Integer) map.get(INFO_TYPE);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，项目名
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(C_NAME).append(field);
            } else if (FieldEnum.PROJECT_NAME.getField().equals(field)) {
                sql.append(",p.project_name as ").append(field);
            } else if (FieldEnum.INFO_TYPE.getField().equals(field)) {
                sql.append(",(CASE WHEN d.info_type = '1' THEN '优良信息' WHEN d.info_type = '2' THEN '负面信息' ELSE '' END ) AS info_type ");
            } else if (FieldEnum.SCORE_TYPE_ID.getField().equals(field)) {
                sql.append(",s.score_name as score_type_id");
            } else {
                sql.append(",d.").append(field).append(" as ").append(field);
            }
        }
        sql.append(" from score_detail d left join company_project p on d.project_id=p.id");
        sql.append(" left join company_base_info c on d.corp_id=c.id");
        sql.append(" left join score_type s on s.id = d.score_type_id");
        //只显示项目审批通过的数据
        sql.append(" where d.state in (2,4,5,6,7)");
        commonScoreDetail(sql, area, infoType, qyName, proName, street, beginTime, endTime, district);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 优良负面信息数据数量
     */
    public String countScoreDetail(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String qyName = (String) map.get(QY_NAME);
        String proName = (String) map.get(PRO_NAME);
        String area = (String) map.get(AREA);
        Integer infoType = (Integer) map.get(INFO_TYPE);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，项目名
        sql.append(SELECT_COUNT);
        sql.append(" from score_detail d left join company_project p on d.project_id=p.id");
        sql.append(" left join company_base_info c on d.corp_id=c.id");
        sql.append(" left join score_type s on s.id = d.score_type_id");
        //只显示项目审批通过的数据
        sql.append(" where d.state in (2,4,5,6,7)");
        commonScoreDetail(sql, area, infoType, qyName, proName, street, beginTime, endTime, district);
        return sql.toString();
    }

    /**
     * 优良负面信息 通用查询条件
     *
     * @param sql      语句
     * @param area     地区
     * @param infoType 消息类型
     * @param qyName   企业名
     * @param proName  项目名
     */
    private void commonScoreDetail(StringBuffer sql, String area, Integer infoType, String qyName, String proName, String street, String beginTime, String endTime, Integer district) {
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and IF(d.project_id=0, (CASE c.community WHEN 0 THEN FIND_IN_SET(c.street,#{area}) ELSE FIND_IN_SET( c.community,#{area}) end),");
            sql.append("(CASE p.community WHEN 0 THEN FIND_IN_SET(p.street,#{area}) ELSE FIND_IN_SET(p.community,#{area}) end))");
        }
        if (infoType != null) {
            sql.append(" and d.info_type=#{infoType}");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%',#{proName},'%')");
        }
        if (district != null) {
            sql.append(" and IF(d.project_id=0,c.district =#{district},p.district = #{district})");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and IF(d.project_id=0,c.street=#{street},p.street =#{street})");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and d.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and d.gmt_create<=#{endTime}");
        }
    }

    /**
     * 企业教育情况数据
     */
    public String listTrains(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String[] fields = (String[]) map.get(FIELDS);
        String qyName = (String) map.get(QY_NAME);
        String area = (String) map.get(AREA);
        Page page = (Page) map.get(PAGE_PARAM);
        String department = (String) map.get(DEPARTMENT);
        String subject = (String) map.get(SUBJECT);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，人员名
        sql.append(SELECT_CID);
        for (String field : fields) {
            if (FieldEnum.CORP_NAME.getField().equals(field)) {
                sql.append(C_NAME).append(field);
            } else if (FieldEnum.PROJECT_NAME.getField().equals(field)) {
                sql.append(",pro.project_name as ").append(field);
            } else if (FieldEnum.PERSON_NAME.getField().equals(field)) {
                sql.append(",p.`name` as ").append(field);
            } else if (FieldEnum.SUBJECT_TYPE.getField().equals(field)) {
                sql.append(",(CASE WHEN subject_type = '1' THEN '项目经理培训' ELSE '其他' END ) AS subject_type");
            } else if (FieldEnum.DEPARTMENT.getField().equals(field)) {
                sql.append(",(SELECT name FROM dic_info WHERE code = department) AS department");
            } else {
                sql.append(",t.").append(field).append(" as ").append(field);
            }
        }
        sql.append(" from company_train t left join company_base_info c on t.corp_id=c.id");
        sql.append(" left join company_person p on t.person_id=p.id");
        sql.append(" left join company_project pro on pro.id=p.project_id");
        //只显示审批通过的数据
        sql.append(" where t.state=2");
        commonTrain(sql, area, qyName, department, subject, street, beginTime, endTime, district);
        sql.append(LIMIT);
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 企业教育情况数据
     */
    public String countTrain(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer();
        String qyName = (String) map.get(QY_NAME);
        String area = (String) map.get(AREA);
        String department = (String) map.get(DEPARTMENT);
        String subject = (String) map.get(SUBJECT);
        Integer district = (Integer) map.get(DISTRICT);
        String street = (String) map.get(STREET);
        String beginTime = (String) map.get(BEGIN_TIME);
        String endTime = (String) map.get(END_TIME);
        //默认显示企业名，人员名
        sql.append(SELECT_COUNT);
        sql.append(" from company_train t left join company_base_info c on t.corp_id=c.id");
        sql.append(" left join company_person p on t.person_id=p.id");
        sql.append(" left join company_project pro on pro.id=p.project_id");
        //只显示审批通过的数据
        sql.append(" where t.state=2");
        commonTrain(sql, area, qyName, department, subject, street, beginTime, endTime, district);
        return sql.toString();
    }

    /**
     * 企业教育情况 通用查询
     *
     * @param sql        语句
     * @param area       地区
     * @param qyName     企业名
     * @param department 培训部门
     * @param subject    培训科目
     */
    private void commonTrain(StringBuffer sql, String area, String qyName, String department, String subject, String street, String beginTime, String endTime, Integer district) {
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE c.community WHEN 0 THEN c.street in (").append(area).append(") ELSE c.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(department)) {
            sql.append(" and t.department =#{department}");
        }
        if (StringUtils.isNotBlank(subject)) {
            sql.append(" and t.subject like CONCAT('%',#{subject},'%')");
        }
        if (district != null) {
            sql.append(" and c.district=#{district}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and c.street=#{street}");
        }
        if (StringUtils.isNotBlank(beginTime)) {
            sql.append(" and t.gmt_create>=#{beginTime}");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(" and t.gmt_create<=#{endTime}");
        }
    }
}
